﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace TextMIS
{
    class registopr:Base
    {
        ArrayList arryList = new ArrayList();
        StringBuilder sql;

        public int Exec_ArryList()
        {
            if (arryList.Count == 0)
            {
                return 0;
            }
            return base.SqlDBAgent.sqlupdate(arryList);
        }
        /// <summary>
        /// sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        public void str_Sqltxt(string strSql)
        {
            arryList.Add(strSql);
        }
        //********************************************************************
        public string Get_MaxStr(string table, string column)
        {
            string strSql;
            strSql = "select max(isnull(" + column + ",0))+1 from " + table + "";
            return base.SqlDBAgent.sqlvalue(strSql).ToString();
        }
        //**************************************************************
        public DataTable Get_tj_dw(int i, string str_bh, int qybz)
        {
            string strSql = "";
            if (qybz == 1)//启用
            {
                if (i == 0)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=4 and qybz=1 order by bh desc";
                }
                if (i == 1)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=8 and qybz=1 and bh like '" + str_bh + "%' order by bh desc";
                }
                if (i == 2)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=12 and qybz=1 and bh like '" + str_bh + "%' order by bh desc";
                }
                if (i == 3)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=16 and qybz=1 and bh like '" + str_bh + "%' order by bh desc";
                }
            }
            else//所有
            {
                if (i == 0)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=4 order by bh desc";
                }
                if (i == 1)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=8 and bh like '" + str_bh + "%' order by bh desc";
                }
                if (i == 2)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=12 and bh like '" + str_bh + "%' order by bh desc";
                }
                if (i == 3)
                {
                    strSql = "select bh,mc,qybz from v_tj_dw where len(bh)=16 and bh like '" + str_bh + "%' order by bh desc";
                }
            }
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_dw(string bh)
        {
            string strSql;
            strSql = "select bh,qybz,mc,pyjm,wbjm,dwfzr,lxdh,czdh,yzbm,lxdz,ywyy,yhzh,qyxz,jzrs,bz,jjlx,hy,qygm,scgrs,yhzyrs,lx from tj_dw where bh='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public int Exists_tj_dw(string dwbh)
        {
            string strSql;
            strSql = "select count(1) from tj_tjdjb where dwbh like '" + dwbh + "%'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public int Delete_tj_dw(string bh)
        {
            string strSql;
            strSql = "delete tj_dw where bh like '" + bh + "%'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public string Get_proc_get_tjdwbh(string dwbh)
        {
            string strSql;
            strSql = "exec proc_get_tjdwbh '" + dwbh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }
        public int Insert_tj_dw(string bh, string qybz, string mc, string pyjm, string wbjm, string dwfzr, string lxdh, string czdh,
            string yzbm, string lxdz, string ywyy, string yhzh, string qyxz, string jzrs, string bz, string jjlx, string hy, string qygm, string scgrs, string yhzyrs, string lx)
        {
            string strSql;
            strSql = "delete tj_dw where bh='" + bh + "';";
            strSql = strSql + "insert tj_dw(bh,qybz,mc,pyjm,wbjm,dwfzr,lxdh,czdh,yzbm,lxdz,ywyy,yhzh,qyxz,jzrs,bz,jjlx,hy,qygm,scgrs,yhzyrs,lx) select '" + bh + "','" + qybz + "','" + mc + "','" + pyjm
                + "','" + wbjm + "','" + dwfzr + "','" + lxdh + "','" + czdh + "','" + yzbm + "','" + lxdz + "','" + ywyy + "','" + yhzh + "','" + qyxz + "','" + jzrs + "','" + bz + "',"
            + "'" + jjlx + "','" + hy + "','" + qygm + "','" + scgrs + "','" + yhzyrs + "','" + lx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //**************************************************************
        public DataTable Get_TJ_DW()
        {
            string strSql = "";
            strSql = "select bh,mc from tj_dw where len(bh)=4 and qybz=1 order by bh desc";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_DWFZ_HD(string dwbh)
        {
            string strSql;
            strSql = "select bh,fzmc from TJ_DWFZ_HD where dwbh='" + dwbh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_DWFZ_HD(string dwbh, string bh)
        {
            string strSql;
            strSql = "select bh,fzmc,xb,zw,zc from TJ_DWFZ_HD where dwbh='" + dwbh + "' and bh='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Delete_TJ_DWFZ_HD(string dwbh, string bh)
        {
            string strSql;
            strSql = "delete TJ_DWFZ_DT where dwbh='" + dwbh + "' and fzbh='" + bh + "';";
            strSql = strSql + "delete TJ_DWFZ_HD where dwbh='" + dwbh + "' and bh='" + bh + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public string Get_proc_get_tjdwfzbh(string dwbh)
        {
            string strSql;
            strSql = "exec proc_get_tjdwfzbh '" + dwbh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }
        public void str_Delete_TJ_DWFZ_HD(string bh, string dwbh)
        {
            string strSql;
            strSql = "delete TJ_DWFZ_DT where dwbh='" + dwbh + "' and fzbh='" + bh + "';";
            strSql = strSql + "delete TJ_DWFZ_HD where dwbh='" + dwbh + "' and bh='" + bh + "'";
            arryList.Add(strSql);
        }
        public void str_Insert_TJ_DWFZ_HD(string bh, string dwbh, string fzmc, string xb, string zw, string zc)
        {
            string strSql;
            strSql = "insert TJ_DWFZ_HD(bh,dwbh,fzmc,xb,zw,zc) select '" + bh + "','" + dwbh + "','" + fzmc + "','" + xb
                + "','" + zw + "','" + zc + "'";
            arryList.Add(strSql);
        }
        public void str_Insert_TJ_DWFZ_DT(string bh, string dwbh, string tjxm)
        {
            string strSql;
            strSql = "insert TJ_DWFZ_DT(fzbh,dwbh,tjxm) select '" + bh + "','" + dwbh + "','" + tjxm + "'";
            arryList.Add(strSql);
        }
        public DataTable Get_TJ_DWFZ_DT(string bh, string dwbh)//分组项目
        {
            string strSql;
            strSql = "select a.tjxm zhxm,b.mc from tj_dwfz_dt a join tj_zhxm_hd b on a.tjxm=b.bh where a.fzbh='" + bh + "' and dwbh='" + dwbh + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_Exists_TJ_TJDJB(string xm, string sfzh)//同名确认
        {
            string strSql;
            strSql = "select tjbh,tjcs,djlsh from tj_tjdjb where xm='" + xm + "' or sfzh='" + sfzh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //**************************************************************

        public string GetDwbh()
        {
            string sql = "select max(bh) bh from tj_dw where len(bh)=4 and bh <>'9999'";
            DataTable dt = new DataTable();
            dt = base.SqlDBAgent.GetDataTable(sql);
            string bh;
            if (dt.Rows.Count <= 0)
            {
                return "0001";
            }
            bh = dt.Rows[0][0].ToString().Trim();
            if (bh == null || bh == "")
            {
                return "0001";
            }
            decimal decBh = Convert.ToDecimal(bh) + 1;
            bh = decBh.ToString();
            string str = "";
            for (int i = 0; i < 4 - bh.Length; i++)
            {
                str += "0";
            }

            return str + bh;
        }
        //删除注册表
        public int Delete_Reg(string id, string cnt)
        {
            string strSql;
            strSql = "delete PE_Regist where Reg_Id='" + id + "' and Count='" + cnt + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        /// <summary>
        /// 体检登记insert
        /// </summary>       
        public void str_Insert_PEReg(string regid, string Ringno,string cnt, string PEdate, string regdt, string Regname, string sex, string birth, string age,
            string Merr, string index, string nation, string IDnum, string PersonT,string mobile, string address, 
            string petyp, string dwbh, string PackID, string PEstatu, string userid, string jsfs, string Edud)
        {
            string strSql;
            strSql = "insert PE_Regist(Reg_Id,RingNum,Count,PE_date,Reg_date,Reg_Name,Reg_Sex,Reg_Birth,Reg_Age,Merriage,sykh,Reg_Nation,IDCardNum,PersonTyp,mobile,address,PE_Type,dwbh,PackageID,PE_stau,UserId,jsfs,EduDegr) select '" +
                regid + "','"+ Ringno + "','" + cnt + "','" + PEdate + "','" + regdt + "','" + Regname + "','" + sex + "','" + birth + "','" + age + "','" +
                Merr + "','" + index + "','" + nation + "','" + IDnum + "','" + PersonT + "','" + mobile + "','" + address + "','" +
                petyp + "','" + dwbh + "','" + PackID + "','" + PEstatu + "','" + userid + "','" + jsfs + "','" + Edud + "'";
            arryList.Add(strSql);
        }

        /***********新增：mars**************/
        //插入
        public int str_Insert_TJ_TJDJB(string tjbh, string tjcs, string tjrq, string djrq, string xm, string xb, string csrq, string nl,
           string hyzk, string sykh, string mz, string sfzh, string rylb, string phone, string mobile, string address, string tjlb,
           string djlsh, string dwbh, string bmbh, string fzbh, byte[] picture, string sumover, string djry, string jsfs, string whcd, string gz, string whys, string gl, string sfbz, string bzhy, string sszl, string jhgl)
        {
            string strSql;
            strSql = "insert tj_tjdjb(tjbh,tjcs,tjrq,djrq,xm,xb,csrq,nl,hyzk,sykh,mz,sfzh,rylb,phone,mobile,address,tjlb,djlsh,dwbh,bmbh,fzbh,picture,sumover,djry,jsfs,whcd,gz,whys,gl,sfbz,bzhy,sszl,jhgl) values( '" +
                tjbh + "','" + tjcs + "','" + tjrq + "','" + djrq + "','" + xm + "','" + xb + "','" + csrq + "','" + nl + "','" +
                hyzk + "','" + sykh + "','" + mz + "','" + sfzh + "','" + rylb + "','" + phone + "','" + mobile + "','" + address + "','" + tjlb + "','" +
                djlsh + "','" + dwbh + "','" + bmbh + "','" + fzbh + "'," + "@pic" + ",'" + sumover + "','" + djry + "','" + jsfs + "','" + whcd + "','" + gz + "','" + whys + "','" + gl + "','" + sfbz + "','" + bzhy + "','" + sszl + "','" + jhgl + "')";
            if (picture == null) picture = new byte[0];
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@pic", picture) };

            return base.SqlDBAgent.sqlupdate(strSql, para);
        }

        public int str_Insert_TJ_ZYB_RYXX(string tjbh, string tjcs, string xm, string xb, string sfzh, string csnyr, string dw, string dwdh,
          string gh, string bh, string tbtq, string lx, string jws, string bm, string zdrq, string zddw, string sfqy,
          string ccnl, string jq, string zq, string tjnl, string xyzn, string lccs, string zccs, string sccs, string yctc, string sfxy, string xyns, string sfyj, string yjsl, string yjsj, string qt,
            string jtbs, string dwdz, string gz, string ygzdwjgz, string zybwhjcs, string csd, string mz, string hy, string zgl, string jsgl, string dhqk, string xysl)
        {
            string strSql;
            strSql = "insert TJ_ZYB_RYXX(tjbh,tjcs,xm,xb,sfzh,csnyr,dw,dwdh,gh,bh,tbrq,lx,jws,bm,zdrq,zddw,sfqy,ccnl,jq,zq,tjnl,xyzn,lccs,zccs,sccs,yctc,sfxy,xyns,sfyj,yjsl,yjsj,qt,jtbs,dwdz,gz,ygzdwjgz,zybwhjcs,csd,mz,hy,zgl,jsgl,dhqk,xysl) values( '" +
                tjbh + "','" + tjcs + "','" + xm + "','" + xb + "','" + sfzh + "','" + csnyr + "','" + dw + "','" +
                dwdh + "','" + gh + "','" + bh + "','" + tbtq + "','" + lx + "','" + jws + "','" + bm + "','" + zdrq + "','" + zddw + "','" +
                sfqy + "','" + ccnl + "','" + jq + "','" + zq + "','" + tjnl + "','" + xyzn + "','" + lccs + "','" + zccs + "','" + sccs + "','" + yctc + "','" + sfxy + "','" + xyns + "','" + sfyj + "','" + yjsl + "','" + yjsj +
                "','" + qt + "','" + jtbs + "','" + dwdz + "','" + gz + "','" + ygzdwjgz + "','" + zybwhjcs + "','" + csd + "','" + mz + "','" + hy + "','" + zgl + "','" + jsgl + "','" + dhqk + "','" + xysl + "')";
            return base.SqlDBAgent.sqlupdate(strSql);
        }

        public int Update_TJ_ZYB_RYXX(string tjbh, string tjcs, string xm, string xb, string sfzh, string csnyr, string dw, string dwdh,
          string gh, string bh, string tbtq, string lx, string jws, string bm, string zdrq, string zddw, string sfqy,
          string ccnl, string jq, string zq, string tjnl, string xyzn, string lccs, string zccs, string sccs, string yctc, string sfxy, string xyns, string sfyj, string yjsl, string yjsj, string qt,
            string jtbs, string dwdz, string gz, string ygzdwjgz, string zybwhjcs, string csd, string mz, string hy, string zgl, string jsgl, string dhqk, string xysl)
        {
            string strSql;
            strSql = "update TJ_ZYB_RYXX set xm='" + xm + "',xb='" + xb + "',sfzh='" + sfzh + "',csnyr='" + csnyr + "',dw='" + dw + "',dwdh='" + dwdh
                + "',tbrq='" + tbtq + "',lx='" + lx + "',jws='" + bm + "',gz='" + gz + "',mz='" + mz
                + "',hy='" + hy + "',zgl='" + zgl + "',jsgl='" + jsgl + "',dhqk='" + dhqk
                + "' where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }

        //更新
        public int Update_TJ_TJDJB(string tjbh, string tjcs, string tjrq, string xm, string xb, string csrq, string nl,
           string hyzk, string sykh, string mz, string sfzh, string rylb, string phone, string mobile, string address, string tjlb,
           string dwbh, string bmbh, string fzbh, byte[] picture, string whcd, string gz, string whys, string gl, string sfbz, string bzhy, string sszl, string jhgl)
        {
            string strSql;
            strSql = "update tj_tjdjb set tjrq='" + tjrq + "',xm='" + xm + "',xb='" + xb + "',csrq='" + csrq + "',nl='" + nl + "',hyzk='" + hyzk
                + "',sykh='" + sykh + "',mz='" + mz + "',sfzh='" + sfzh + "',rylb='" + rylb + "',phone='" + phone + "',mobile='" + mobile + "',address='" + address
                + "',tjlb='" + tjlb + "',dwbh='" + dwbh + "',bmbh='" + bmbh + "',fzbh='" + fzbh + "',picture=@pic,whcd='" + whcd + "',gz='" + gz + "',whys='" + whys + "',gl='" + gl + "',sfbz='" + sfbz + "',bzhy='" + bzhy + "',sszl='" + sszl + "',jhgl='" + jhgl + "'"
                + " where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' ";
            if (picture == null) picture = new byte[0];
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@pic", picture) };

            return base.SqlDBAgent.sqlupdate(strSql, para);
        }
        /*************************/

        public int Update_TJ_TJDJB(string tjbh, string tjcs, string tjrq, string xm, string xb, string csrq, string nl,
            string hyzk, string sykh, string mz, string sfzh, string rylb, string phone, string mobile, string address, string tjlb,
            string dwbh, string bmbh, string fzbh, string picture, string bzhy, string sszl, string jhgl)
        {
            string strSql;
            strSql = "update tj_tjdjb set tjrq='" + tjrq + "',xm='" + xm + "',xb='" + xb + "',csrq='" + csrq + "',nl='" + nl + "',hyzk='" + hyzk
                + "',sykh='" + sykh + "',mz='" + mz + "',sfzh='" + sfzh + "',rylb='" + rylb + "',phone='" + phone + "',mobile='" + mobile + "',address='" + address + "',bzhy='" + bzhy + "',sszl='" + sszl
                + "',tjlb='" + tjlb + "',dwbh='" + dwbh + "',bmbh='" + bmbh + "',fzbh='" + fzbh + "',picture='" + picture + "',jhgl='" + jhgl
                + "' where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //获取体检登记表信息
        public DataTable Get_Reginfo(string date)
        {
            string strSql;
            strSql = "select RingNum,Reg_Name,Reg_Sex,Reg_Age,PE_date,Reg_date,Reg_Id,Count,PE_stau,bzhy,sszl from PE_Regist  where Reg_date>='" + date + "'  order by Reg_Id asc";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //可以有两种方式
        public DataTable Get_Reginfo(string regdate, string regid, string name, string memo)
        {
            string strSql;
            //if (sfbz == "1")  //已收费
            //{
            //    strSql = "select djlsh,xm,xb,nl,tjrq,djrq,a.tjbh,a.tjcs,dwmc,bmmc,sumover,isnull(b.sfh,0) sfh from v_tj_tjdjb a left join tj_sfb b on a.tjbh=b.tjbh and a.tjcs=b.tjcs where isnull(b.sfh,0)>0 and tjrq>='" + tjrq + "' and tjrq<='" + tjrq + "' and (djlsh='" + djh + "' or a.tjbh='" + djh + "' or 1=1) and (xm like'%" + xm + "%' or 1=1) and isnull(b.sfh,0)=isnull(b.ysfh,0) order by djlsh asc";
            //}
            //else             //未收费
            //{
            if (name == "")
            {

                    strSql = "select djlsh,xm,xb,nl,tjrq,djrq,tjbh,tjcs,dwmc,bmmc,sumover from v_tj_tjdjb  where  tjrq>='" + regdate + "'  order by djlsh asc";              
            }
            else
            {

                    strSql = "select djlsh,xm,xb,nl,tjrq,djrq,tjbh,tjcs,dwmc,bmmc,sumover from v_tj_tjdjb where tjrq>='" + regdate + "'  and  (xm like'%" + name + "%') order by djlsh asc";

            }

            //}
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public DataTable GetSsje(string sfh, string tjbh, string tjcs)
        {
            string strSql;
            strSql = "select ysje,ssje,yhlx,yhxx from tj_sfb where sfh='" + sfh + "' and tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }



        public DataTable Get_TJ_TJDJB(string tjrq1, string tjrq2, string dwbh, string bmbh)
        {
            string strSql;
            if (bmbh == "")
            {
                strSql = "select xm '姓名',xb '性别',nl '年龄',csrq '出生日期',hyzk '婚姻状况',mobile '手机号码',phone '联系电话',sfzh '身份证号',bmmc '部门',"
                    + "rylb '人员类别',sykh '索引卡号',address '联系地址',mz '民族' from v_tj_tjdjb where tjrq>='"
                    + tjrq1 + "' and tjrq<='" + tjrq2 + "' and dwbh='" + dwbh + "' order by tjrq desc, djlsh";
            }
            else
            {
                strSql = "select xm '姓名',xb '性别',nl '年龄',csrq '出生日期',hyzk '婚姻状况',mobile '手机号码',phone '联系电话',sfzh '身份证号',bmmc '部门',"
                    + "rylb '人员类别',sykh '索引卡号',address '联系地址',mz '民族' from v_tj_tjdjb where tjrq>='"
                    + tjrq1 + "' and tjrq<='" + tjrq2 + "' and dwbh='" + dwbh + "' and isnull(bmbh,'')='" + bmbh + "' order by tjrq desc, djlsh";
            }
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_TJDJB(string tjrq1, string tjrq2, string xm, string dwbh, string bmbh, string xsfs)
        {
            //xsfs 0全部 1个人 2单位
            string strSql;
            if (xsfs == "1")
            {
                strSql = "select djlsh,xm,xb,nl,tjbh,tjcs,dwmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='9999' order by tjrq desc,djlsh desc";
            }
            else if (xsfs == "2")
            {
                if (bmbh == "")
                {
                    strSql = "select djlsh,xm,xb,nl,tjbh,tjcs,dwmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' order by tjrq desc,djlsh desc";
                }
                else
                {
                    strSql = "select djlsh,xm,xb,nl,tjbh,tjcs,dwmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and isnull(bmbh,'')='" + bmbh + "' order by tjrq desc,djlsh desc";
                }
            }
            else
            {
                strSql = "select djlsh,xm,xb,nl,tjbh,tjcs,dwmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' order by tjrq desc, djlsh";
            }
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public DataTable Get_TJ_TJDJB(string tjrq1, string tjrq2, string tjbh1, string tjbh2, string xm, string xb, string dwbh, string bmbh, string zjzt)
        {
            //zjzt 0全部 1未总检 2总检
            string strSql;
            if (tjbh1 != "" && tjbh2 != "")
            {
                if (dwbh != "")
                {
                    if (zjzt == "2")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                    }
                    else if (zjzt == "1")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                    }
                    else
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') order by tjrq desc,djlsh desc";
                        }

                    }
                }
                else
                {
                    if (zjzt == "2")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                    }
                    else if (zjzt == "1")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                    }
                    else
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and tjbh>='" + tjbh1 + "' and tjbh<='" + tjbh2 + "' and xm like '%" + xm + "%' order by tjrq desc,djlsh desc";
                        }
                    }
                }
            }
            else
            {
                if (dwbh != "")
                {
                    if (zjzt == "2")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                    }
                    else if (zjzt == "1")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                    }
                    else
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "'and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and dwbh='" + dwbh + "' and (isnull(bmbh,'')='" + bmbh + "' or bmbh='') order by tjrq desc,djlsh desc";
                        }
                    }
                }
                else
                {
                    if (zjzt == "2")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and sumover in(2) order by tjrq desc,djlsh desc";
                        }
                    }
                    else if (zjzt == "1")
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and sumover in(0,1) order by tjrq desc,djlsh desc";
                        }
                    }
                    else
                    {
                        if (xb != "%")
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' and xbcode='" + xb + "' order by tjrq desc,djlsh desc";
                        }
                        else
                        {
                            strSql = "select 0 selected,djlsh,xm,xb,nl,tjbh,tjcs,tjrq,dwmc,tcmc,sfzh,sumover,sfbz from v_tj_tjdjb where tjrq>='" + tjrq1 + "' and tjrq<='" + tjrq2 + "' and xm like '%" + xm + "%' order by tjrq desc,djlsh desc";
                        }
                    }
                }
            }
            return base.SqlDBAgent.GetDataTable(strSql);
        }


        //通过id和次数获取体检登记信息.
        public DataTable Get_PERegInfo(string id, string cnt)
        {
            string strSql;
            strSql = "select RingNum,Reg_id,Count,Reg_Name,IDCardNum,Reg_Sex,Reg_Age,Reg_Nation,Reg_Birth,dwbh,PE_date,Merriage,sykh,PersonTyp,phone,mobile,address,PE_Type,bmbh,PackageID,picture,PE_stau,EduDegr,gz,whys,gl,sfbz,bzhy,sszl,jhgl from PE_Regist where Reg_id='"
                + id + "' and Count='" + cnt + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public string Get_TJ_DW(string bh)
        {
            string strSql;
            strSql = "select mc from tj_dw where bh='" + bh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }

        //获取体检状态 .
        public int Get_Statu(string regid, string count)
        {
            string strSql;
            strSql = "select PE_stau from PE_Regist where Reg_id='" + regid + "' and Count='" + count + "'";
            return Convert.ToInt32((base.SqlDBAgent.sqlvalue(strSql).ToString().Trim()));
        }
        public DataTable Get_dt_tj_tjjlb(string tjbh, string tjcs)//体检项目
        {
            string strSql;
            strSql = "select a.tjxmbh zhxm,b.mc from tj_tjjlb a join tj_zhxm_hd b on a.tjxmbh=b.bh where tjbh='"
                + tjbh + "' and tjcs='" + tjcs + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        /// <summary>
        /// 获取体检费用信息
        /// </summary>
        /// <param name="tjbh"></param>
        /// <param name="tjcs"></param>
        /// <returns></returns>
        public DataTable GetTjfyxx(string tjbh, string tjcs)
        {
            string strSql;
            strSql = "select distinct a.tjxmbh zhxm,b.mc,a.xmdj,a.dzbl,hj=isnull(a.xmdj,0)*case when a.dzbl=0 then 1 else isnull(a.dzbl,1) end,case isnull(sfh,0) when 0 then 0 else 1 end sfh,a.CHARGED from tj_tjjlb a join tj_zhxm_hd b on a.tjxmbh=b.bh left join tj_sfb c on a.tjbh=c.tjbh and a.tjcs=c.tjcs where a.tjbh='"
                + tjbh + "' and a.tjcs='" + tjcs + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public int Delete_tj_tjjlb(string tjbh, string tjcs, string tjxmbh)
        {
            string strSql;
            strSql = "delete tj_tjjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and tjxmbh='" + tjxmbh + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public void str_Delete_tj_tjjlb(string tjbh, string tjcs, string tjxmbh)
        {
            string strSql;
            strSql = "delete tj_tjjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and tjxmbh='" + tjxmbh + "'";
            arryList.Add(strSql);
        }
        public void str_Insert_tj_tjjlb(string xh, string tjbh, string tjcs, string lxbh, string ydtjrq, string tjxmbh, string xmdj, string isover,
            string jsfs, string sflb, string zxks, string xmlx)
        {
            string strSql;
            strSql = "insert tj_tjjlb(xh,tjbh,tjcs,lxbh,ydtjrq,tjxmbh,xmdj,isover,jsfs,sflb,zxks,xmlx) select '" +
                xh + "','" + tjbh + "','" + tjcs + "','" + lxbh + "','" + ydtjrq + "','" + tjxmbh + "','" + xmdj + "','" + isover + "','" +
                jsfs + "','" + sflb + "','" + zxks + "','" + xmlx + "'";
            arryList.Add(strSql);
        }
        public bool Existes_tj_tjjlb(string tjbh, string tjcs, string tjxmbh)//是否存在该组合项目ID
        {
            string strSql;
            strSql = "select count(1) from tj_tjjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and tjxmbh='" + tjxmbh + "'";
            int i = Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
            if (i > 0)
                return true;
            else
                return false;
        }
        public DataTable Get_tj_dwfz_hd(string dwbh)//分组
        {
            string strSql;
            if (dwbh == "")
                strSql = "select bh,fzmc mc from tj_dwfz_hd where dwbh=''";
            else
                strSql = "select bh,fzmc mc from tj_dwfz_hd where dwbh='" + dwbh.Substring(0, 4) + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_dwfz_dt(string fzbh)//分组编号
        {
            string strSql;
            strSql = "select a.tjxm zhxm,b.mc from tj_dwfz_dt a join tj_zhxm_hd b on a.tjxm=b.bh where a.fzbh='" + fzbh + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_zhxm_hd(string bh)
        {
            string strSql;
            strSql = "select tjlx,jcjylx,dj,bh,sflb from tj_zhxm_hd where bh='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_TJDJB_XM(string xm)
        {
            string strSql;
            strSql = "select 0 selected,a.djlsh,a.xm,e.xmmc xb,a.nl,a.sfzh,a.tjrq,isnull(c.mc,'个人体检') dwmc,d.mc bmmc,a.tjbh,a.tjcs from tj_tjdjb a join (select xm,tjbh,max(tjcs) as tjcs from tj_tjdjb where xm='"
                + xm + "' group by xm,tjbh) as b on a.xm=b.xm and a.tjbh=b.tjbh and a.tjcs=b.tjcs left join tj_dw c on a.dwbh=c.bh left join tj_dw d on a.bmbh=d.bh join xt_zdxm e on a.xb=e.bzdm where e.zdflbm=1";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_TJDJB_SFZH(string sfzh)
        {
            string strSql;
            strSql = "select 0 selected,a.djlsh,a.xm,e.xmmc xb,a.nl,a.sfzh,a.tjrq,isnull(c.mc,'个人体检') dwmc,c.mc bmmc,a.tjbh,a.tjcs from tj_tjdjb a join (select sfzh,tjbh,max(tjcs) as tjcs from tj_tjdjb where sfzh='"
                + sfzh + "' group by sfzh,tjbh) as b on a.sfzh=b.sfzh and a.tjbh=b.tjbh and a.tjcs=b.tjcs left join tj_dw c on a.dwbh=c.bh left join tj_dw d on a.bmbh=d.bh join xt_zdxm e on a.xb=e.bzdm where e.zdflbm=1";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public string Get_proc_get_djlsh(string tjrq)//根据体检日期，获取登记流水号
        {
            string strSql;
            strSql = "exec proc_get_djlsh '" + tjrq + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }

        //获取套餐和项目信息,
        public DataTable Get_Package(string id)
        {
            string strSql;
            strSql = "select bh,fzmc mc from tj_dwfz_hd where dwbh='" + id + "' union select convert(varchar(10),bh) bh,mc from tj_tc_hd";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //性别检查
        public int CheckSex(string zhxm, string xb)
        {
            string strSql;
            strSql = "select count(1) from GrpItemRel where Grp_Id='" + zhxm + "' and Itm_id in(select Item_id from ItemInfo where SexAttr='" + xb + "' or SexAttr='%')";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public int Exec_tj_tjdjb_Img(string column, string tjbh, string tjcs, byte[] image)
        {
            string strSql;
            if (column == "picture")
                strSql = "update tj_tjdjb set picture=@image where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            else if (column == "barcode")
                strSql = "update tj_tjdjb set barcode=@image where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            else
                return 0;

            SqlParameter sp_image = new SqlParameter("@image", SqlDbType.Image);
            sp_image.Value = image;

            SqlParameter[] objParameterName = new SqlParameter[] { sp_image };
            return base.SqlDBAgent.sqlupdate(strSql, objParameterName);
        }
        public int GetTjCounts()
        {
            string strSql;
            strSql = "select count(1) from tj_tjdjb";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }

        /// <summary>
        /// 获取操作员id
        /// </summary>
        /// <param name="zjm"></param>
        /// <returns></returns>
        public string GetCzyid(string zjm)
        {
            string sql = "select czyid from xt_czy where czybm='" + zjm + "' or czymc='" + zjm + "'";
            DataTable dt = new DataTable();
            dt = base.SqlDBAgent.GetDataTable(sql);
            if (dt.Rows.Count <= 0)
            {
                return "";
            }
            return dt.Rows[0][0].ToString().Trim();
        }

        /// <summary>
        /// 根据条件获取体检登记信息
        /// </summary>
        /// <param name="djlsh">登记流水号</param>
        /// <param name="sfzh">身份证号</param>
        /// <param name="from">开始日期</param>
        /// <param name="to">结束日期</param>
        /// <param name="xm">姓名</param>
        /// <param name="dwbh">单位编号</param>
        /// <param name="tjlb">体检类别</param>
        /// <returns></returns>
        public DataTable GetTjdjxxByCondition(string djlsh, string sfzh, string from, string to, string xm, string dwbh, string tjlb)
        {
            sql = new StringBuilder("select '0' x  ,TJLB,XM,XB,SFZH,TJBH,djlsh,tjcs	 from v_tj_tjdjb where lbbh in('01','02','03') and tjrq>='" + from + "' and tjrq<='" + to + "' ");
            if (djlsh != "")
            {
                sql.Append(" and djlsh like '" + djlsh + "%'");
            }
            if (sfzh != "")
            {
                sql.Append(" and sfzh like '" + sfzh + "%'");
            }
            if (xm != "")
            {
                sql.Append(" and xm like '" + xm + "%'");
            }
            if (dwbh != "")
            {
                sql.Append(" and dwbh='" + dwbh + "'");
            }
            sql.Append(" order by tjbh desc");

            return base.SqlDBAgent.GetDataTable(sql.ToString());
        }

        /// <summary>
        /// 根据体检编号获取体检信息
        /// </summary>
        /// <param name="tjbh"></param>
        /// <returns></returns>
        public DataTable GetTjxxByTjbh(string tjbh)
        {
            sql = new StringBuilder("SELECT TJBH ,SFZH ,XM ,XB ,NL  ,ADDRESS ,PHONE ,TJCS ,DJLSH ,TJRQ,dwbh,csrq csnyr FROM TJ_TJDJB  where tjbh='" + tjbh + "'");

            return base.SqlDBAgent.GetDataTable(sql.ToString());
        }

        public DataTable GetTjdjxx(string dwbh, string from, string to, string xm)
        {
            sql = new StringBuilder("  SELECT  0 as xz,a.TJBH ,a.TJCS ,a.XM ,a.XB,a.NL ,TJRQ ,jktj as tjbz,SFZH ,DJLSH,isnull(jkzbh,'0') jkzbh,isnull(a.gz,'0') gz,b.fzdw,b.fzrq,fzr,hy,hylx,ksxq,jsxq,hjd,xjd,dw,sfbz,b.gz bzgz,a.dwmc,bzhy,sszl,dycs     FROM v_tj_tjdjb a left join tj_jkzmb b on a.tjbh=b.tjbh and  a.tjcs=b.tjcs  where isnull(jktj,0)<>0");
            sql.Append(" and isnull(jktj,'0')='001' and tjrq>='" + from + "' and tjrq<='" + to + "'");
            if (dwbh != "")
            {
                sql.Append(" and dwbh='" + dwbh + "'");
            }
            if (xm != "")
            {
                sql.Append(" and a.xm like '%" + xm + "%'");
            }

            sql.Append(" order by a.tjbh desc");
            return base.SqlDBAgent.GetDataTable(sql.ToString());
        }

        public DataTable GetTjdjxx_jkzpldy(string dwbh, string from, string to, string xm)
        {
            sql = new StringBuilder("  SELECT  0 selected,a.TJBH ,a.TJCS ,a.XM ,a.XB,a.NL ,isnull(a.gz,'0') gz,b.gz bzgz,a.dwmc,bzhy,sszl,TJRQ ,SFZH ,DJLSH,b.fzdw,b.fzrq,fzr,sfbz,isnull(jkzbh,'0') jkzbh,hy,hylx,ksxq,jsxq,hjd,xjd,dw,jktj as tjbz,dycs    FROM v_tj_tjdjb a   join tj_jkzmb b on a.tjbh=b.tjbh and  a.tjcs=b.tjcs  where isnull(jktj,0)<>0");
            sql.Append(" and isnull(jktj,'0')='001' and tjrq>='" + from + "' and tjrq<='" + to + "'");
            if (dwbh != "")
            {
                sql.Append(" and dwbh='" + dwbh + "'");
            }
            if (xm != "")
            {
                sql.Append(" and a.xm like '%" + xm + "%'");
            }

            sql.Append(" order by a.tjbh desc");
            return base.SqlDBAgent.GetDataTable(sql.ToString());
        }

        /// <summary>
        /// 保存体检费用信息
        /// </summary>
        /// <param name="tjbh">体检编号</param>
        /// <param name="tjcs">体检次数</param>
        /// <param name="tjxmbh">体检项目编号</param>
        /// <param name="jsr">结算人</param>
        /// <param name="jsrq">结算日期</param>
        /// <param name="sjh">收费号</param>
        //public void UpdateTjFyxx(Tjjlb tjjl, List<string> sqls)
        //{
        //    sql = new StringBuilder("update tj_tjjlb set CHARGED='1'");
        //    sql.Append(",jsr='" + tjjl.Jsr + "'");
        //    sql.Append(",jsrq='" + tjjl.Jsrq + "'");
        //    sql.Append(",sjh='" + tjjl.Sjh + "'");
        //    sql.Append("where tjbh='" + tjjl.Tjbh + "' and tjcs='" + tjjl.Tjcs + "' and tjxmbh='" + tjjl.Tjxmbh + "'");
        //    sqls.Add(sql.ToString());
        //}

        /// <summary>
        /// 保存体检费用信息
        /// </summary>
        /// <param name="tjjls"></param>
        /// <returns></returns>
        //public int SaveTjfyxx(List<Tjjlb> tjjls)
        //{
        //    List<string> sqls = new List<string>();
        //    for (int i = 0; i < tjjls.Count; i++)
        //    {
        //        UpdateTjFyxx(tjjls[i], sqls);
        //    }

        //    return base.SqlDBAgent.sqlupdate(sqls);
        //}

        /// <summary>
        /// 体检独立收费
        /// </summary>
        /// <returns></returns>
        public int TjSf(string sfh, string tjbh, string tjcs, string sfy, decimal ysje, decimal ssje, int yhlx, decimal yhxx, string ysfh, string beizhu)
        {
            string sql = "";
            sql = "Insert into tj_sfb(sfh,tjbh,tjcs,sfy,ysje,ssje,yhlx,yhxx,ysfh,beizhu) values('" + sfh + "','" + tjbh + "','" + tjcs + "','" + sfy + "','" + ysje + "','" + ssje + "'," + yhlx + ",'" + yhxx + "','" + ysfh + "','" + beizhu + "')";
            return base.SqlDBAgent.sqlupdate(sql);
        }
        /// <summary>
        /// 体检退费更新收费表收费号
        /// </summary>
        /// <returns></returns>
        public int UpdateSfb(string sfh, string tjbh, string tjcs, string ysfh)
        {
            string sql = "";
            sql = "update tj_sfb set ysfh='" + ysfh + "' where sfh='" + sfh + "' and tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.sqlupdate(sql);
        }

        /// <summary>
        /// 体检独立退费
        /// </summary>
        /// <returns></returns>
        public int TjTf(string tfdh, string sfy, decimal tfje, string tfyy, string ysfh)
        {
            string sql = "";
            sql = "Insert into tj_tfb(tfdh,tfry,tfje,tfyy,ysfh) values('" + tfdh + "','" + sfy + "','" + tfje + "','" + tfyy + "','" + ysfh + "')";
            return base.SqlDBAgent.sqlupdate(sql);
        }

        /// <summary>
        /// 体检健康证保存
        /// </summary>
        /// <returns></returns>
        public int TjJkzSave(string jkzbh, string tjbh, string tjcs, string xm, string nl, string xb, string fzdw, string fzrq, string fzr, string hy, string hylx, string ksxq, string jsxq, string hjd, string xjd, string dw, string gz)
        {
            string sql = "";
            sql = "Insert into tj_jkzmb(jkzbh,tjbh,tjcs,xm,nl,xb,fzdw,fzrq,fzr,hy,hylx,ksxq,jsxq,hjd,xjd,dw,gz) values('" + jkzbh + "','" + tjbh + "','" + tjcs + "','" + xm + "','" + nl + "','" + xb + "','" + fzdw + "','" +
                fzrq + "','" + fzr + "','" + hy + "','" + hylx + "','" + ksxq + "','" + jsxq + "','" + hjd + "','" + xjd + "','" + dw + "','" + gz + "')";
            return base.SqlDBAgent.sqlupdate(sql);
        }
        /// <summary>
        /// 体检健康证更新
        /// </summary>
        /// <returns></returns>
        public int TjJkzSaveAs(string jkzbh2, string jkzbh, string tjbh, string tjcs, string xm, string nl, string xb, string fzdw, string fzrq, string fzr, string hy, string hylx, string ksxq, string jsxq, string hjd, string xjd, string dw)
        {
            string sql = "";
            sql = "Update tj_jkzmb set jkzbh='" + jkzbh2 + "',xm='" + xm + "',nl='" + nl + "',xb='" + xb + "',fzrq='" + fzrq + "',hy='" + hy + "',hylx='" + hylx + "',hjd='" + hjd + "',xjd='" +
                xjd + "',dw='" + dw + "' where jkzbh='" + jkzbh + "' and tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.sqlupdate(sql);
        }

        /// <summary>
        /// 体检收费查询
        /// </summary>
        /// <returns></returns>
        public int TjSfCx(string tjbh, string tjcs)
        {
            string sql = "";
            sql = "select isnull(count(*),0) sl from tj_sfb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and  sfh not in (select ysfh from tj_tfb) and ysje>0";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(sql));
        }

        /// <summary>
        /// 待办证人员信息
        /// </summary>
        /// <returns></returns>
        public DataTable Get_TJ_Bzryxx(string djh, string xm, string sfbz, string beizhu)
        {
            string strSql;
            if (sfbz == "1")  //已收费
            {
                strSql = "select djlsh djlsh_bz,xm xm_bz,xb xb_bz,nl nl_bz,tjrq tjrq_bz,djrq djrq_bz,a.tjbh tjbh_bz,a.tjcs tjcs_bz,dwmc dwmc_bz,bmmc bmmc_bz,sumover sumover_bz,isnull(b.sfh,0) sfh_bz from v_tj_tjdjb a left join tj_sfb b on a.tjbh=b.tjbh and a.tjcs=b.tjcs where isnull(b.sfh,0)>0  and (djlsh='" + djh + "' or a.tjbh='" + djh + "' or 1=1) and (xm like'%" + xm + "%' or 1=1) and isnull(b.sfh,0)=isnull(b.ysfh,0) and sumover=2   order by djlsh asc";
            }
            else             //未收费
            {
                strSql = "select djlsh djlsh_bz,xm xm_bz,xb xb_bz,nl nl_bz,tjrq tjrq_bz,djrq djrq_bz,a.tjbh tjbh_bz,a.tjcs tjcs_bz,dwmc dwmc_bz,bmmc bmmc_bz,sumover sumover_bz,isnull(b.sfh,0) sfh_bz from v_tj_tjdjb a left join tj_sfb b on a.tjbh=b.tjbh and a.tjcs=b.tjcs where isnull(b.sfh,0)=0  and (djlsh='" + djh + "' or a.tjbh='" + djh + "' or 1=1) and (xm like'%" + xm + "%' or 1=1) and isnull(b.sfh,0)=isnull(b.ysfh,0) and sumover=2  order by djlsh asc";
            }
            return base.SqlDBAgent.GetDataTable(strSql);
        }


        /// <summary>
        /// 体检办证打印查询
        /// </summary>
        /// <returns></returns>
        public DataTable TjJkzbb(string jkzbh, string tjbh, string tjcs)
        {
            string sql = "";
            sql = "select jkzbh,a.xm,a.xb,a.nl,a.gz,case c.jktj when '001' then '合格' when '002' then '不合格' when '003' then '缺项' when '004' then '复查' when '005' then '已复查' else '' end jktj,a.fzrq,a.fzdw,a.dw,czymc as fzr,picture from tj_jkzmb a " +
                    "left join tj_tjdjb c on a.tjbh=c.tjbh and a.tjcs=c.tjcs " +
                    "left join xt_czy d on a.fzr=d.czyid " +
                    "where jkzbh='" + jkzbh + "' and a.tjbh='" + tjbh + "' and a.tjcs='" + tjcs + "'";
            return base.SqlDBAgent.GetDataTable(sql);
        }

        /// <summary>
        /// 体检健康证打印次数保存
        /// </summary>
        /// <returns></returns>
        public int TjJkzDycsSave(string jkzbh, string tjbh, string tjcs, int dycs)
        {
            string sql = "";
            sql = "update tj_jkzmb set dycs=dycs+" + dycs + " where jkzbh='" + jkzbh + "' and tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";
            return base.SqlDBAgent.sqlupdate(sql);
        }

        /// <summary>
        /// 根据身份证计算年龄
        /// </summary>
        /// <returns></returns>
        public int GetNl(string sfzh)
        {
            string sql = "";
            if (sfzh.Length == 18)
            {
                sql = "select datediff(year,substring('" + sfzh + "',7,8),getdate())";
            }
            else //15
            {
                sql = "select datediff(year,substring('" + sfzh + "',7,6),getdate())";
            }
            return Convert.ToInt16(base.SqlDBAgent.sqlvalue(sql));
        }
        //体检检索
        public DataTable Get_RetrReg(string fiter)//快速检索
        {
            string strSql;
            strSql = "select Reg_Id,Count,PE_stau,sfbz from PE_Regist where Reg_name='" + fiter + "' or RingNum='" + fiter + "' or Reg_Id='" + fiter + "' or sykh='" + fiter + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        /// 获取体检记录表
        /// </summary>
        /// <param name="tjbh">体检编号</param>
        /// <param name="tjcs">体检次数</param>
        /// <param name="lxbh">科室编码</param>
        /// <returns></returns>
        public DataTable Get_TJ_TJJLB(string tjbh, string tjcs, string lxbh)
        {
            string strSql;
            strSql = "select b.mc,a.tjxmbh zhxm,a.xh,a.isover,a.xmlx,a.zxks from tj_tjjlb a join tj_zhxm_hd b on a.tjxmbh=b.bh where a.tjbh='" + tjbh + "' and a.tjcs='" + tjcs + "'and lxbh='" + lxbh + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        /// <summary>
        /// 获取体检记录表
        /// </summary>
        /// <param name="tjbh">体检编号</param>
        /// <param name="tjcs">体检次数</param>
        /// <param name="xh">记录序号</param>
        /// <param name="tjzhxm">体检组合项目ID</param>
        /// <returns></returns>
        public DataTable Get_TJ_TJJLB(string tjbh, string tjcs, string xh, string tjzhxm)
        {
            string strSql;
            strSql = "select xj,jcrq,jcys,czy from tj_tjjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'and xh='" + xh + "' and tjxmbh='" + tjzhxm + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        /// <summary>
        /// 获取体检记录明细表
        /// </summary>
        /// <param name="xh">序号</param>
        /// <param name="tjxm">体检组合项目</param>
        /// <returns></returns>
        public DataTable Get_TJ_TJJLMXB(string xh, string tjzhxm)
        {
            string strSql;
            strSql = "select b.mc,a.jg,a.sfyx,a.jrxj,a.mcjrxj,b.dw,'' ckxx,'' cksx,a.ts,a.tjzhxm,a.tjxm,b.zcts zcjg,a.tjlx,'' ckz,a.xh,'' keyword from tj_tjjlmxb a join tj_tjxmb b on a.tjxm=b.tjxm where a.xh='" + xh + "' and a.tjzhxm='" + tjzhxm + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        /// <summary>
        /// 获取参考值
        /// </summary>
        /// <param name="tjlx">体检类型</param>
        /// <param name="tjxm">体检项目ID</param>
        /// <param name="xb">性别</param>
        /// <param name="nl">年龄</param>
        /// <returns></returns>
        public DataTable Get_pro_get_ckz(string tjlx, string tjxm, string xb, int nl, string gz)
        {
            string strSql;
            strSql = "exec pro_get_ckz '" + tjlx + "','" + tjxm + "','" + xb + "','" + gz + "'," + nl;
            return base.SqlDBAgent.GetDataTable(strSql);
        }


        /// <summary>
        /// 获取乙肝两对半诊断
        /// </summary>
        /// <returns></returns>
        public string Get_TJ_HSB_DT(string bh, string ckz)
        {
            string strSql;
            strSql = "select mc+'|'+zdbh from tj_hsb_dt where bh='" + bh + "' and ckz='" + ckz + "'";
            try
            {
                return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
            }
            catch
            {
                return "|";
            }
        }
        public void str_Update_tj_tjjlb(string tjbh, string tjcs, string xh, string tjzhxm)
        {
            string strSql;
            strSql = "update tj_tjjlb set isover=0,jcrq=null,jcys=null,xj=null where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and xh='" + xh + "' and tjxmbh='" + tjzhxm + "'";
            arryList.Add(strSql);
        }
        //更新体检记录明细表
        public void str_Update_tj_tjjlmxb(string xh, string tjzhxm)
        {
            string strSql;
            strSql = "update a set a.jg=b.zcts,a.jcrq=null,a.jcys=null,a.jrxj=0,a.mcjrxj=0,a.sfyx=0,a.dw=null,a.ts=null,a.ckz=null from tj_tjjlmxb a join tj_tjxmb b on a.tjxm=b.tjxm where a.xh='" + xh + "' and a.tjzhxm='" + tjzhxm + "'";
            arryList.Add(strSql);
        }
        //删除体检记录表
        public void str_Delete_tj_jbjlb(string tjbh, string tjcs, string tjzhxm)
        {
            string strSql;
            strSql = "delete tj_jbjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and zhxmbh='" + tjzhxm + "'";
            arryList.Add(strSql);
        }
        //更新体检记录表
        public void str_Update_tj_tjjlb(string tjbh, string tjcs, string xh, string tjzhxm, string isover, string jcrq, string jcys, string xj, string czy)
        {
            string strSql;
            strSql = "update tj_tjjlb set isover='" + isover + "',jcrq='" + jcrq + "',jcys='" + jcys + "',xj='" + xj + "',czy='" + czy + "' where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and xh='" + xh + "' and tjxmbh='" + tjzhxm + "'";
            arryList.Add(strSql);
        }
        //更新体检记录明细表
        public void str_Update_tj_tjjlmxb(string xh, string tjxm, string jg, string tjzhxm, string tjlx, string jcrq, string jcys, string jrxj, string mcjrxj, string sfyx, string dw, string ckz, string ts)
        {
            string strSql;
            strSql = "update tj_tjjlmxb set jg='" + jg + "',jcrq='" + jcrq + "',jcys='" + jcys + "',jrxj='" + jrxj + "',mcjrxj='" + mcjrxj + "',sfyx='" + sfyx
                + "',dw='" + dw + "',ckz='" + ckz + "',ts='" + ts + "' where xh='" + xh + "' and tjxm='" + tjxm + "' and tjzhxm='" + tjzhxm + "'";
            arryList.Add(strSql);
        }

        //插入疾病记录表
        public void str_Insert_tj_jbjlb(string jbxh, string tjbh, string tjcs, string djlsh, string tjlx, string zhxmbh, string tjxmbh, string jbbh, string jbmc, string jcys)
        {
            string strSql;
            strSql = "insert tj_jbjlb(jbxh,tjbh,tjcs,djlsh,tjlx,zhxmbh,tjxmbh,jbbh,jbmc,jcys) select '" + jbxh + "','" + tjbh + "','" + tjcs + "','" + djlsh
                + "','" + tjlx + "','" + zhxmbh + "','" + tjxmbh + "','" + jbbh + "','" + jbmc + "','" + jcys + "'";
            arryList.Add(strSql);
        }
        //更新疾病登记表
        public void str_Update_tj_tjdjb(string tjbh, string tjcs, string sumover)
        {
            string strSql;
            strSql = "update tj_tjdjb set sumover='" + sumover + "' where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and sumover='0'";
            arryList.Add(strSql);
        }


        /// <summary>
        /// 获取参考值结果
        /// </summary>
        /// <param name="tjlx">体检类型</param>
        /// <param name="tjxm">体检项目ID</param>
        /// <param name="xb">性别</param>
        /// <param name="nl">年龄</param>
        /// <param name="jg">结果</param>
        /// <returns></returns>
        public string Get_pro_get_ckz_jg(string tjlx, string tjxm, string xb, int nl, double jg, string gz)
        {
            string strSql;
            strSql = "exec pro_get_ckz_jg '" + tjlx + "','" + tjxm + "','" + xb + "'," + nl + ",'" + gz + "'," + jg;
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }
        //体检疾病记录表
        public DataTable Get_tj_jbjlb(string tjbh, string tjcs, string zhxmbh)
        {
            string strSql;
            strSql = "select jbmc,jbbh,tjxmbh from tj_jbjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and zhxmbh='" + zhxmbh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //体检疾病记录表疾病编号
        public string Get_tj_jbjlb_jbbh(string tjbh, string tjcs, string tjlx, string zhxmbh, string tjxmbh)
        {
            string strSql;
            strSql = "select jbbh from tj_jbjlb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "' and tjlx='" + tjlx + "' and zhxmbh='" + zhxmbh + "' and tjxmbh='" + tjxmbh + "'";
            try
            {
                return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
            }
            catch
            {
                return "";
            }
        }

        //体检建议
        public DataTable get_tj_suggestion(string tjlx, string bh)
        {
            string strSql;
            strSql = "select keyword,bh from tj_suggestion where tjlx='" + tjlx + "' and bh='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //体检建议疾病编号
        public string Get_tj_suggestion_jbbh(string tjlx, string keyword)
        {
            string strSql;
            strSql = "select bh from tj_suggestion where tjlx='" + tjlx + "' and ltrim(rtrim(keyword))='" + keyword + "'";
            try
            {
                return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
            }
            catch
            {
                return "";
            }
        }
        //组合项目-正常小结（未见明显异常）
        public string Get_ZHXM_ZCXJ(string zhxm)
        {
            string strSql;
            strSql = "select zcxj from tj_zhxm_hd where bh='" + zhxm + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }




        //函数主档表
        public DataTable Get_TJ_HSB_HD(string zhxmbh)
        {
            string strSql;
            strSql = "select bh,mc from tj_hsb_hd where zhxmbh='" + zhxmbh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public string Get_TJ_HSB_XMDZ_DZM(string mc)
        {
            string strSql;
            strSql = "select dzm from tj_hsb_xmdz where mc='" + mc + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }

        public string Get_pro_get_tzzs(decimal tzzs)//获取体重指数诊断
        {
            string strSql;
            strSql = "exec pro_get_tzzs " + tzzs;
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();//
        }
        public string Get_pro_get_xyzs(decimal ssy, decimal szy, int xyty)//获取血压指数诊断
        {
            string strSql;
            strSql = "exec pro_get_xyzs " + ssy + "," + szy + "," + xyty;
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }
        public int Exists_SGTZXY(string zhxmbh)//判断是否为肥胖，血压
        {
            string strSql;
            strSql = "select count(1) from tj_hsb_hd where zhxmbh='" + zhxmbh + "' and (mc like '%肥胖诊断功能%' or mc like '%血压诊断功能%')";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        //体检登记信息
        public DataTable GetTjdjxx(string tjbh, string tjcs)
        {
            string strSql = "select tjlb,gz,rylb from v_tj_tjdjb where tjbh='" + tjbh + "' and tjcs='" + tjcs + "'";

            return base.SqlDBAgent.GetDataTable(strSql);
        }

    }
}
